package com.huayue.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Component;

import com.huayue.entity.RoomPredictOrder;
import com.huayue.entity.RoomStatus;
import com.huayue.util.DateHelper;
import com.huayue.util.PageHelper;
import com.mysql.DBHelper;

/**
 * 预定单
 */
@Component
public class RoomPredictOrderDao implements BaseDao<RoomPredictOrder> {

    @Override
    public int insert(RoomPredictOrder entity) {

        return DBHelper.executeUpdate(
            "INSERT INTO room_predict_order (ordernumber, customer_id, predict_in_time, predict_out_time, totalprice, remark,days) VALUES (?, (select max(id) from customer), ?, ?, ?, ?,?);",
            entity.getOrderNumber(), entity.getPredictInTime(), entity.getPredictOutTime(), entity.getTotalPrice(),
            entity.getRemark() == "" ? "" : entity.getRemark(), entity.getDays());
    }

    @Override
    public int update(RoomPredictOrder entity) {
        return 0;
    }

    @Override
    public int delete(RoomPredictOrder entity) {
        // 获取本订单的预定信息，判断房态表中的该房间是否是预定单中的预定时间，
        // 如果是则将其房态改为未入住，清空intime,outtime
        String sql1 = "select room_id,predict_in_time,predict_out_time from room_predict_order rpo\n"
            + "right join room_predict_order_details rpod on rpod.room_predict_order_id=rpo.id  where rpo.id = ?";
        List<Map<String, Object>> list = DBHelper.executeQuery(sql1, entity.getId());
        for (Map<String, Object> map : list) {
            DBHelper.executeUpdate(
                "update room_status set in_time=null,out_time=null,status_id=1,customer_id=null where (room_id=? and in_time=?)",
                map.get("room_id"), map.get("predict_in_time"));
        }
        DBHelper.executeUpdate("update room_predict_order_details set is_status=9 where room_predict_order_id=?",
            entity.getId());
        return DBHelper.executeUpdate("update room_predict_order set is_status=9 where id=?", entity.getId());
    }

    @Override
    public List<Map<String, Object>> findAll(Object... obj) {
        return DBHelper.executeQuery("select * from room_predict_order where is_deleted=0 ");
    }

    @Override
    public List<Map<String, Object>> findPage(PageHelper page, Object... obj) {
        RoomPredictOrder room_predict_order = (RoomPredictOrder)obj[0];
        String sql = "select rpo.*,c.realname,c.phone from room_predict_order rpo\n"
            + " left join customer c on rpo.customer_id = c.id  where rpo.is_deleted = 0";
        if (room_predict_order != null && !"".equals(room_predict_order.getOrderNumber())) {
            sql += " and concat(c.realname,c.idnumber,c.phone,rpo.ordernumber) like '%"
                + room_predict_order.getOrderNumber() + "%' ";
        }
        sql += " order by rpo.id desc limit ?,? ";
        return DBHelper.executeQuery(sql, page.getBegin(), page.getPageSize());
    }

    @Override
    public Map<String, Object> findOne(RoomPredictOrder entity) {
        return DBHelper.executeQuery(
            "select rpo.*,c.realname,c.phone,c.idnumber,c.sex from room_predict_order rpo left join customer c on rpo.customer_id = c.id where rpo.is_deleted=0 and rpo.id=?",
            entity.getId()).get(0);
    }

    @Override
    public int count(Object... obj) {
        RoomPredictOrder room_predict_order = (RoomPredictOrder)obj[0];
        String sql = " select count(*) counts from room_predict_order rpo\n"
            + " left join customer c on rpo.customer_id = c.id  where rpo.is_deleted = 0";
        if (room_predict_order != null && !"".equals(room_predict_order.getOrderNumber())) {
            sql += " and concat(c.realname,c.idnumber,c.phone,rpo.ordernumber) like '%"
                + room_predict_order.getOrderNumber() + "%' ";
        }
        sql += " order by rpo.id desc";
        return Integer.valueOf(DBHelper.executeQuery(sql).get(0).get("counts").toString());

    }

    // 查询可用房间"and (rst.id = 1 or rst.id = 4)
    public List<Map<String, Object>> findAvailRoom(String checkDate, Integer days) {
        String outDate = DateHelper.plusDayShort(days, checkDate);
        String sql = "select r.id id,r.name name,rt.id rtid,rt.name rtname,rt.price,rs.status_id from room_type rt\n"
            + "left join room r on rt.id = r.room_type_id left join room_status rs on rs.room_id=r.id\n"
            + "left join room_status_type rst on rs.status_id=rst.id \n"
            + "left join customer c on rs.customer_id=c.id  where r.is_deleted=0\n" + "and rst.id = 1 or ('" + outDate
            + "'<rs.in_time or '" + checkDate + "'>rs.out_time) ";
        List<Map<String, Object>> finalList = DBHelper.executeQuery(sql);
        // 查询预定单中的信息
        String sqlpredict = "select rs.room_id from room_predict_order rpo\n"
            + "RIGHT JOIN room_predict_order_details rpod on rpod.room_predict_order_id=rpo.id\n"
            + "inner join room_status rs on rs.room_id = rpod.room_id where rpod.is_status=0 and ('" + outDate
            + "'>=rpo.predict_in_time or '" + checkDate + "'<=rpo.predict_out_time) ";
        List<Map<String, Object>> listpre = DBHelper.executeQuery(sqlpredict);
        if (listpre.size() == 0) {
            return finalList;
        }
        for (Map<String, Object> map : listpre) {
            for (Map<String, Object> map2 : finalList) {
                if (map.get("room_id").toString().equals(map2.get("id").toString())) {
                    map2.put("status_id", 3);
                }
            }
        }
        return finalList;
    }

    // 入住成功后，修改room_status表中的字段
    public int updateRoomStatus(RoomStatus roomStatus) {
        // 查询该房间是否已入住有人/若已入住则不修改房态信息
        String sqlfind = "select * from room_status where room_id=" + roomStatus.getId();
        Map<String, Object> map = DBHelper.executeQuery(sqlfind).get(0);
        if ("2".equals(map.get("status_id").toString())) {
            return 1;
        }
        String sql =
            "update room_status set customer_id=(select max(id) from customer),status_id = 3,in_time=?,out_time=? where room_id = ?";
        return DBHelper.executeUpdate(sql, roomStatus.getInTime(), roomStatus.getOutTime(), roomStatus.getId());
    }

    /**
     * 根据房间ID查询预定信息
     *
     * @param id
     * @return
     */
    public Map<String, Object> findPredictByRoomId(String id) {
        String sql =
            "select c.realname,c.phone,rt.id rtid,rpo.ordernumber,r.name,rpod.price allprice,rpo.predict_in_time,"
                + "rpo.predict_out_time,rt.price rtprice from room_predict_order rpo \n"
                + "left join room_predict_order_details rpod on rpod.room_predict_order_id=rpo.id\n"
                + "left join room r on r.id=rpod.room_id left join room_type rt on rt.id=rpod.room_type_id\n"
                + " left join customer c on rpo.customer_id =c.id where rpod.is_status=0 and r.id=?";
        return DBHelper.executeQuery(sql, id).get(0);
    }

    /**
     * 根据电话号查询订单信息
     *
     * @param phone
     * @return
     */
    public List<Map<String, Object>> findPredictOrderByPhone(String phone) {
        String sql =
            "select rpo.*,c.realname,c.phone,rt.name tname,count(rpod.id) room_number from room_predict_order rpo\n"
                + "right join room_predict_order_details rpod on rpod.room_predict_order_id=rpo.id\n"
                + "left join room_type rt on rpod.room_type_id=rt.id left join customer c on rpo.customer_id=c.id\n"
                + "where rpo.is_status = 0 and c.phone=? group by rpo.id";
        return DBHelper.executeQuery(sql, phone);
    }

    /**
     * 将将预定订单的状态改为1 根据roomid 预定订单详情的状态改为1 根据roomid
     *
     * @param roomId
     * @return
     */
    public int updatePredictOrderStatus(Integer roomId) {
        String sql = "update room_predict_order_details set is_status = 1 where id=(select d.id from \n"
            + "(select id from room_predict_order_details rd where rd.room_id = ? ORDER BY id desc limit 1) d)";
        DBHelper.executeUpdate(sql, roomId);
        String sql2 = "update room_predict_order set is_status = 1 where id=(select d.room_predict_order_id from \n"
            + "(select room_predict_order_id from room_predict_order_details rd where rd.room_id = ? "
            + " ORDER BY id desc limit 1) d)";
        return DBHelper.executeUpdate(sql2, roomId);
    }

    /**
     * 定时将已预定但时间到期的房间状态更改为空房
     */
    public int updatePredictOrderNotCheckInAUTO() {
        // 定时将已预定但时间到期的房间状态更改为空房
        String sql = "update room_status set status_id=1 where room_id "
            + " in(select d.room_id from (select rpod.room_id from room_predict_order rpo \n"
            + " right join room_predict_order_details rpod on rpod.room_predict_order_id=rpo.id\n"
            + " where rpod.is_status=0 and predict_out_time < ?)d)";
        DBHelper.executeUpdate(sql, new Date());

        // 更改预定详情状态
        String sqldetails = "update room_predict_order_details set is_status = 9 \n"
            + "where id in (select d.id from (select rpod.id from room_predict_order_details rpod \n"
            + "left join room_predict_order rpo on rpo.id=rpod.room_predict_order_id\n"
            + "where rpod.is_status = 0 and rpo.predict_out_time < ? )d)";
        DBHelper.executeUpdate(sqldetails, new Date());

        // 更改预定订单状态
        String sqlOrder = "update room_predict_order set is_status = 9 \n"
            + " where id in (select d.id from (select id from room_predict_order rpo \n"
            + " where rpo.is_status = 0 and predict_out_time < ? )d)";

        return DBHelper.executeUpdate(sqlOrder, new Date());

    }

    /**
     * 定时将已预定的房间信息更新到房态表中
     * 
     * @return
     */
    public int updateRoomStatusByPredictAUTO() {
        int suc = 0;
        String sql =
            "select rpo.predict_in_time,rpo.predict_out_time,rpod.room_id,rpo.customer_id from room_predict_order rpo\n"
                + "right join room_predict_order_details rpod on rpod.room_predict_order_id=rpo.id\n"
                + "where rpo.is_status=0 \n"
                + "and predict_in_time<now() and predict_in_time >(select subdate(now(),interval 13 hour))";
        List<Map<String, Object>> list = DBHelper.executeQuery(sql);
        String sqlUpdate =
            "update room_status set quit=0,status_id=3,in_time=?,out_time=?,customer_id=? where room_id=?";
        for (Map<String, Object> map : list) {
            suc += DBHelper.executeUpdate(sqlUpdate, map.get("predict_in_time"), map.get("predict_out_time"),
                map.get("customer_id"), map.get("room_id"));
        }
        return suc;
    }
}
